import pymysql
import pandas as pd
from matplotlib import pyplot as plt
import numpy as np

# 连接数据库
conn = 'mysql+pymysql://red:2003721%40chen@106.15.46.141:3306/red'

# 查询数据
query_inventory = "SELECT * FROM product_inventory WHERE seller_no = 'seller_33'"
query_inventory_detail = "SELECT * FROM inventory"
query_price = "SELECT * FROM product_price"
query_prediction = "SELECT * FROM prediction WHERE seller_no = 'seller_33'"

inventory_df = pd.read_sql(query_inventory, conn)
inventory_detail_df = pd.read_sql(query_inventory_detail, conn)
price_df = pd.read_sql(query_price, conn)
prediction_df = pd.read_sql(query_prediction, conn)

# 合并数据
data = pd.merge(inventory_df, inventory_detail_df, on='warehouse_no', how='left')
data = pd.merge(data, price_df, on='product_no', how='left')
data = pd.merge(data, prediction_df, on=['seller_no', 'product_no', 'warehouse_no', 'date'], how='left')

# Step 4: 定义参数
H = 0.2  # 持有成本率
B = 0.5  # 缺货成本率
initial_inventory = 5  # 初始库存
LT = 3  # 提前期，固定为3天
NRT = 1  # 库存盘点周期，固定为1天

# Step 5: 初始化库存和补货策略
data['inventory_begin'] = initial_inventory
data['inventory_end'] = initial_inventory  # 初始化 inventory_end 列
data['lower_s'] = initial_inventory * 0.2  # 动态设置补货下限为初始库存的20%
data['upper_s'] = initial_inventory * 1.5  # 动态设置补货上限为初始库存的150%
data['replenish_qty'] = 0  # 补货量
data['forecast_qty'] = data['forecast_qty'].fillna(0)  # 填补空白预测值
data['forecast_qty'] = np.where(data['forecast_qty'] == 0, np.random.randint(1, 5, size=len(data)),
                                data['forecast_qty'])  # 随机生成缺失的预测数据

# Step 6: 模拟补货策略
for i in range(1, len(data)):
    # 每天的库存开始值
    data.loc[i, 'inventory_begin'] = data.loc[i - 1, 'inventory_end']

    # 根据需求预测更新库存
    forecast_qty = data.loc[i, 'forecast_qty']
    inventory_end = data.loc[i, 'inventory_begin'] - forecast_qty

    # 如果库存低于阈值 lower_s，进行补货
    if inventory_end < data.loc[i, 'lower_s']:
        replenish_qty = data.loc[i, 'upper_s'] - inventory_end
        data.loc[i, 'replenish_qty'] = replenish_qty
        inventory_end = data.loc[i, 'upper_s']  # 更新库存至补货上限

    # 更新库存结束值
    data.loc[i, 'inventory_end'] = inventory_end

# Step 7: 筛选指定的商品和仓库数据
filtered_data = data[(data['seller_no'] == 'seller_33') &
                     (data['product_no'] == 'product_1360') &
                     (data['warehouse_no'] == 'wh_54')]

# Step 8: 输出筛选后的结果
filtered_result = filtered_data[['seller_no', 'product_no', 'warehouse_no', 'date', 'lower_s', 'upper_s',
                                 'inventory_begin', 'inventory_end', 'forecast_qty', 'replenish_qty']]
# 按日期升序排列
filtered_result = filtered_result.sort_values(by='date', ascending=True)
filtered_result.to_csv("filtered_replenishment_plan.csv", index=False)

print(filtered_result)

# Step 9: 可视化选择的商品和仓库数据
plt.figure(figsize=(12, 6))
plt.plot(filtered_result['date'], filtered_result['inventory_begin'], label='Inventory Begin', marker='o')
plt.plot(filtered_result['date'], filtered_result['forecast_qty'], label='Forecast Quantity', marker='x')
plt.plot(filtered_result['date'], filtered_result['replenish_qty'], label='Replenish Quantity', marker='s')
plt.xlabel('Date')
plt.ylabel('Quantity')
plt.title('Inventory and Replenishment for product_1360 in wh_54')
plt.legend()
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()
